可用在SQL SERVER 2008

sys.dm_exec_cached_plans
sys.dm_exec_query_stats

SELECT t.[text], s.last_execution_time,db_name(dbid),*
FROM sys.dm_exec_cached_plans AS p
INNER JOIN sys.dm_exec_query_stats AS s
ON p.plan_handle = s.plan_handle
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
--WHERE t.[text] LIKE N'%something unique about your query%'
ORDER BY s.last_execution_time DESC;

sys.dm_exec_cached_plans
sys.dm_exec_query_stats

ref 認識快取儲存
https://dotblogs.com.tw/ricochen/2012/01/11/65237

每十秒實行一次,記錄到table.可以查看誰用掉CPU?執行最久?
SELECT r.scheduler_id as 排程器識別碼,
r.status as 要求的狀態,
r.session_id as SPID,s.host_name,
r.blocking_session_id as BlkBy,
substring(
ltrim(q.text),
r.statement_start_offset/2+1,
(CASE
WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), q.text)) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset)/2)
AS [正在執行的 T-SQL 命令],
r.cpu_time as [CPU Time(ms)],
r.start_time as [開始時間],
r.total_elapsed_time as [執行總時間],
r.reads as [讀取數],
r.writes as [寫入數],
r.logical_reads as [邏輯讀取數],
-- q.text, /* 完整的 T-SQL 指令碼 */
d.name as [資料庫名稱],c.connect_time, s.login_time, c.client_net_address, s.login_name

FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS q
LEFT JOIN sys.databases d ON (r.database_id=d.database_id)
LEFT JOIN sys.dm_exec_sessions s on r.session_id = s.session_id
LEFT JOIN sys.dm_exec_connections c ON r.session_id = c.session_id
WHERE r.session_id > 50 AND r.session_id <> @@SPID